﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using DataAccessLayer;

namespace BusinessLogicLayer
{
    public class clsDataAccess
    {
        #region Properties
        public clsSettings Settings { get; set; }
        private clsDatabase Database { get; set; }
        public clsSQLTranslation SQLTranslation { get; set; }
        #endregion

        #region Constructors
        public clsDataAccess(String pPathExecution)
        {
            this.Settings = new clsSettings(pPathExecution);
        }
        #endregion

        #region Events
        #endregion

        #region Public methods
        /// <summary>
        /// Gets the translation for a control from the database.
        /// </summary>
        /// <param name="pFormName"></param>
        /// <param name="pControlName"></param>
        /// <param name="pControlText"></param>
        /// <param name="pIDUser"></param>
        /// <returns>The translation.</returns>
        public String GetTranslation(String pFormName, String pControlName, String pControlText, Int32 pIDUser)
        {
            String TranslationText = pControlText;
            clsQuery Query;

            //If there is no database, there will be nothing to load.
            if (this.Database != null)
            {
                if (pIDUser > 0)
                {
                    Query = new clsQuery("SELECT Translations.Translation FROM Translations, Users WHERE Translations.IDLanguages = Users.IDLanguages AND Translations.FormName = '" + pFormName + "' AND Translations.ControlName = '" + pControlName + "';", "Get translation.");
                }
                else
                {
                    if (this.Settings.User_Login == null || this.Settings.User_Login.Language == null)
                    {
                        Query = new clsQuery("SELECT Translations.Translation FROM Translations WHERE Translations.IDLanguages = " + GetIDLanguages(pIDUser) + " AND Translations.FormName = '" + pFormName + "' AND Translations.ControlName = '" + pControlName + "';", "Get translation.");
                    }
                    else
                    {
                        Query = new clsQuery("SELECT Translations.Translation FROM Translations WHERE Translations.IDLanguages = " + this.Settings.User_Login.Language.ID + " AND Translations.FormName = '" + pFormName + "' AND Translations.ControlName = '" + pControlName + "';", "Get translation.");
                    }
                }
                DataTable Translations = this.Database.GetTable(Query);
                if (Translations.Rows.Count > 0)
                {
                    foreach (DataRow Translation in Translations.Rows)
                    {
                        TranslationText = (String)Translation["Translation"];
                    }
                }
                else
                {
                    if (InsertTranslation(pFormName, pControlName, GetIDLanguages(pIDUser), pControlText, 0) != true)
                    {
                        throw new ArgumentException("Error 8: Could not insert translation.");
                    }
                }
            }
            return TranslationText;
        }

        /// <summary>
        /// Changes a store in the database.
        /// </summary>
        /// <param name="pID"></param>
        /// <param name="pStoreName"></param>
        /// <param name="pStreet"></param>
        /// <param name="pPostcode"></param>
        /// <param name="pCity"></param>
        /// <param name="pCountry"></param>
        /// <returns>The state of success.</returns>
        public Boolean ChangeStore(Int32 pID, String pStoreName, String pStreet, String pPostcode, String pCity, String pCountry, Boolean pClosed)
        {
            clsQuery Query = new clsQuery("UPDATE Stores SET DateChanged = " + this.SQLTranslation.ActualDate + ", IDUserChanged = " + this.Settings.User_Login.ID + ", StoreName = '" + pStoreName + "', Street = '" + pStreet + "', Postcode = '" + pPostcode + "', City = '" + pCity + "', Country = '" + pCountry + "', Closed = " + ConvertBooleanToNumber(pClosed) + " WHERE ID = " + pID + ";", "Change store.");
            if (this.Database.ExecuteQuery(Query) == 1)
            {
                return true;
            }
            else
            {
                throw new ArgumentException("Error 6: Error changing store.");
            }
        }

        /// <summary>
        /// Changes a finance in the database.
        /// </summary>
        /// <param name="pID"></param>
        /// <param name="pStoreName"></param>
        /// <param name="pStreet"></param>
        /// <param name="pPostcode"></param>
        /// <param name="pCity"></param>
        /// <param name="pCountry"></param>
        /// <returns>The state of success.</returns>
        public Boolean ChangeFinance(Int32 pID, DateTime pDateConcernd, String pDescription, Double pAmount, clsCurrency pCurrency)
        {
            clsQuery Query = new clsQuery("UPDATE Finances SET DateChanged = " + this.SQLTranslation.ActualDate + ", IDUserChanged = " + this.Settings.User_Login.ID + ", DateConcerned = " + this.SQLTranslation.GetDateString(pDateConcernd) + ", Description = '" + pDescription + "', Amount = " + DoubleToDatabaseString(pAmount) + ", IDCurrencies = " + pCurrency.ID + " WHERE ID = " + pID + ";", "Change finance.");
            if (this.Database.ExecuteQuery(Query) == 1)
            {
                return true;
            }
            else
            {
                throw new ArgumentException("Error 15: Error changing finance.");
            }
        }

        /// <summary>
        /// Changes a currency in the database.
        /// </summary>
        /// <param name="pID"></param>
        /// <param name="pStoreName"></param>
        /// <param name="pStreet"></param>
        /// <param name="pPostcode"></param>
        /// <param name="pCity"></param>
        /// <param name="pCountry"></param>
        /// <returns>The state of success.</returns>
        public Boolean ChangeCurrency(Int32 pID, String pCurrencyName, String pCurrencyAbbrevation, Double pCurrencyValue, DateTime pEffectiveFrom)
        {
            clsQuery Query = new clsQuery("UPDATE Currencies SET DateChanged = " + this.SQLTranslation.ActualDate + ", IDUserChanged = " + this.Settings.User_Login.ID + ", CurrencyName = '" + pCurrencyName + "', CurrencyAbbrevation = '" + pCurrencyAbbrevation + "', ExchangeRateToOneEUR = " + DoubleToDatabaseString(pCurrencyValue) + ", DateEffectiveFrom = " + this.SQLTranslation.GetDateString(pEffectiveFrom) + " WHERE ID = " + pID + ";", "Change currency.");
            if (this.Database.ExecuteQuery(Query) == 1)
            {
                return true;
            }
            else
            {
                throw new ArgumentException("Error 17: Error changing currency.");
            }
        }

        /// <summary>
        /// Deletes a finance in the database.
        /// </summary>
        /// <param name="pID"></param>
        /// <param name="pStoreName"></param>
        /// <param name="pStreet"></param>
        /// <param name="pPostcode"></param>
        /// <param name="pCity"></param>
        /// <param name="pCountry"></param>
        /// <returns>The state of success.</returns>
        public Boolean DeleteFinance(Int32 pID)
        {
            clsQuery Query = new clsQuery("DELETE FROM Finances WHERE ID = " + pID + ";", "Delete a finance.");
            if (this.Database.ExecuteQuery(Query) == 1)
            {
                return true;
            }
            else
            {
                throw new ArgumentException("Error 16: Error deleting finance.");
            }
        }

        /// <summary>
        /// Deletes a currency in the database.
        /// </summary>
        /// <param name="pID"></param>
        /// <param name="pStoreName"></param>
        /// <param name="pStreet"></param>
        /// <param name="pPostcode"></param>
        /// <param name="pCity"></param>
        /// <param name="pCountry"></param>
        /// <returns>The state of success.</returns>
        public Boolean DeleteCurrency(Int32 pID)
        {
            clsQuery Query = new clsQuery("DELETE FROM Currencies WHERE ID = " + pID + ";", "Delete a currency.");
            if (this.Database.ExecuteQuery(Query) == 1)
            {
                return true;
            }
            else
            {
                throw new ArgumentException("Error 18: Error deleting currency.");
            }
        }

        /// <summary>
        /// Changes a translation in the database.
        /// </summary>
        /// <param name="pID"></param>
        /// <param name="pStoreName"></param>
        /// <param name="pStreet"></param>
        /// <param name="pPostcode"></param>
        /// <param name="pCity"></param>
        /// <param name="pCountry"></param>
        /// <returns>The state of success.</returns>
        public Boolean ChangeTranslation(Int32 pIDTranslations, String pFormName, String pControlName, String pTranslation)
        {
            clsQuery Query = new clsQuery("UPDATE Translations SET DateChanged = " + this.SQLTranslation.ActualDate + ", IDUserChanged = " + this.Settings.User_Login.ID + ", FormName = '" + pFormName + "', ControlName = '" + pControlName + "', Translation = '" + pTranslation + "' WHERE ID = " + pIDTranslations + ";", "Change translation.");
            if (this.Database.ExecuteQuery(Query) == 1)
            {
                return true;
            }
            else
            {
                throw new ArgumentException("Error 13: Error changing translation.");
            }
        }

        /// <summary>
        /// Changes a language in the database.
        /// </summary>
        /// <param name="pID"></param>
        /// <param name="pStoreName"></param>
        /// <param name="pStreet"></param>
        /// <param name="pPostcode"></param>
        /// <param name="pCity"></param>
        /// <param name="pCountry"></param>
        /// <returns>The state of success.</returns>
        public Boolean ChangeLanguage(Int32 pID, String pLanguageName)
        {
            clsQuery Query = new clsQuery("UPDATE Languages SET DateChanged = " + this.SQLTranslation.ActualDate + ", IDUserChanged = " + this.Settings.User_Login.ID + ", Language = '" + pLanguageName + "' WHERE ID = " + pID + ";", "Change language.");
            if (this.Database.ExecuteQuery(Query) == 1)
            {
                return true;
            }
            else
            {
                throw new ArgumentException("Error 11: Error changing language.");
            }
        }

        /// <summary>
        /// Changes a user in the database.
        /// </summary>
        /// <param name="pID"></param>
        /// <param name="pStoreName"></param>
        /// <param name="pStreet"></param>
        /// <param name="pPostcode"></param>
        /// <param name="pCity"></param>
        /// <param name="pCountry"></param>
        /// <returns>The state of success.</returns>
        public Boolean ChangeUser(Int32 pID, String pFirstName, String pSecondName, String pPassword, clsLanguage pLanguage, clsCurrency pCurrency)
        {
            clsQuery Query = new clsQuery("UPDATE Users SET DateChanged = " + this.SQLTranslation.ActualDate + ", FirstName = '" + pFirstName + "', SecondName = '" + pSecondName + "', IDLanguages = " + pLanguage.ID + ", IDCurrencies = " + pCurrency.ID + " WHERE ID = " + pID + ";", "Change user.");
            if (this.Database.ExecuteQuery(Query) == 1)
            {
                return true;
            }
            else
            {
                throw new ArgumentException("Error 7: Error changing user.");
            }
        }

        /// <summary>
        /// Inserts a store into the database.
        /// </summary>
        /// <param name="StoreName"></param>
        /// <param name="Street"></param>
        /// <param name="Postcode"></param>
        /// <param name="City"></param>
        /// <param name="Country"></param>
        /// <returns>The state of success.</returns>
        public Boolean InsertStore(String pStoreName, String pStreet, String pPostcode, String pCity, String pCountry, Boolean pClosed)
        {
            clsQuery Query = new clsQuery("INSERT INTO Stores (DateCreated, DateChanged, IDUserCreated, IDUserChanged, StoreName, Street, Postcode, City, Country, Closed) VALUES (" + this.SQLTranslation.ActualDate + ", " + this.SQLTranslation.ActualDate + ", " + this.Settings.User_Login.ID + ", " + this.Settings.User_Login.ID + ", '" + pStoreName + "', '" + pStreet + "', '" + pPostcode + "', '" + pCity + "', '" + pCountry + "', " + ConvertBooleanToNumber(pClosed) + ");", "Insert store.");
            if (this.Database.ExecuteQuery(Query) == 1)
            {
                return true;
            }
            else
            {
                throw new ArgumentException("Error 4: Error inserting the store.");
            }
        }

        /// <summary>
        /// Inserts a finance into the database.
        /// </summary>
        /// <param name="StoreName"></param>
        /// <param name="Street"></param>
        /// <param name="Postcode"></param>
        /// <param name="City"></param>
        /// <param name="Country"></param>
        /// <returns>The state of success.</returns>
        public Boolean InsertFinance(DateTime pDateConcernd, String pDescription, Double pAmount, clsCurrency pCurrency)
        {
            clsQuery Query = new clsQuery("INSERT INTO Finances (DateCreated, DateChanged, IDUserCreated, IDUserChanged, DateConcerned, Description, Amount, IDCurrencies) VALUES (" + this.SQLTranslation.ActualDate + ", " + this.SQLTranslation.ActualDate + ", " + this.Settings.User_Login.ID + ", " + this.Settings.User_Login.ID + ", " + this.SQLTranslation.GetDateString(pDateConcernd) + ", '" + pDescription + "', " + DoubleToDatabaseString(pAmount) + ", " + pCurrency.ID + ");", "Insert store.");
            if (this.Database.ExecuteQuery(Query) == 1)
            {
                return true;
            }
            else
            {
                throw new ArgumentException("Error 14: Error inserting the finance.");
            }
        }

        /// <summary>
        /// Inserts a currency into the database.
        /// </summary>
        /// <param name="StoreName"></param>
        /// <param name="Street"></param>
        /// <param name="Postcode"></param>
        /// <param name="City"></param>
        /// <param name="Country"></param>
        /// <returns>The state of success.</returns>
        public Boolean InsertCurrency(String pCurrencyName, String pCurrencyAbbrevation, Double pCurrencyValue, DateTime pEffectiveFrom)
        {
            clsQuery Query = new clsQuery("INSERT INTO Currencies (DateCreated, DateChanged, IDUserCreated, IDUserChanged, CurrencyName, CurrencyAbbrevation, ExchangeRateToOneEUR, DateEffectiveFrom) VALUES (" + this.SQLTranslation.ActualDate + ", " + this.SQLTranslation.ActualDate + ", " + this.Settings.User_Login.ID + ", " + this.Settings.User_Login.ID + ", '" + pCurrencyName + "', '" + pCurrencyAbbrevation + "'," + DoubleToDatabaseString(pCurrencyValue) + ", " + this.SQLTranslation.GetDateString(pEffectiveFrom) + ");", "Insert currency.");
            if (this.Database.ExecuteQuery(Query) == 1)
            {
                return true;
            }
            else
            {
                throw new ArgumentException("Error 16: Error inserting the currency.");
            }
        }

        /// <summary>
        /// Inserts a language into the database.
        /// </summary>
        /// <param name="StoreName"></param>
        /// <param name="Street"></param>
        /// <param name="Postcode"></param>
        /// <param name="City"></param>
        /// <param name="Country"></param>
        /// <returns>The state of success.</returns>
        public Boolean InsertLanguage(String pLanguage)
        {
            clsQuery Query = new clsQuery("INSERT INTO Languages (DateCreated, DateChanged, IDUserCreated, IDUserChanged, Language) VALUES (" + this.SQLTranslation.ActualDate + ", " + this.SQLTranslation.ActualDate + ", " + this.Settings.User_Login.ID + ", " + this.Settings.User_Login.ID + ", '" + pLanguage + "');", "Insert language.");
            if (this.Database.ExecuteQuery(Query) == 1)
            {
                return true;
            }
            else
            {
                throw new ArgumentException("Error 10: Error inserting the language.");
            }
        }

        /// <summary>
        /// Inserts a translation into the database.
        /// </summary>
        /// <param name="pFormName"></param>
        /// <param name="pControlName"></param>
        /// <param name="pIDLanguages"></param>
        /// <param name="pTranslation"></param>
        /// <returns>The state of success.</returns>
        public Boolean InsertTranslation(String pFormName, String pControlName, Int32 pIDLanguages, String pTranslation, Int32 pIDUser)
        {
            clsQuery Query = new clsQuery("INSERT INTO Translations (DateCreated, DateChanged, IDUserCreated, IDUserChanged, FormName, ControlName, IDLanguages, Translation) VALUES (" + this.SQLTranslation.ActualDate + ", " + this.SQLTranslation.ActualDate + ", " + pIDUser + ", " + pIDUser + ", '" + pFormName + "', '" + pControlName + "', " + pIDLanguages + ", '" + pTranslation + "');", "Insert translation.");
            if (this.Database.ExecuteQuery(Query) == 1)
            {
                return true;
            }
            else
            {
                throw new ArgumentException("Error 9: Error inserting translation.");
            }
        }

        /// <summary>
        /// Returns the boolean state if stores were found in the database.
        /// </summary>
        /// <param name="pStoreName"></param>
        /// <param name="pStreet"></param>
        /// <param name="pPostcode"></param>
        /// <param name="pCity"></param>
        /// <param name="pCountry"></param>
        /// <returns>Returns if a store like this exists.</returns>
        public Boolean StoreExists(String pStoreName, String pStreet, String pPostcode, String pCity, String pCountry)
        {
            Int32 StoreQuantity = 0;
            clsQuery Query = new clsQuery("SELECT COUNT(*) AS StoreNumber FROM Stores WHERE StoreName = '" + pStoreName + "' AND Street = '" + pStreet + "' AND Postcode = '" + pPostcode + "' AND City = '" + pCity + "' AND Country = '" + pCountry + "';", "Count stores.");
            DataTable StoreNumber = this.Database.GetTable(Query);
            foreach (DataRow StoreRow in StoreNumber.Rows)
            {
                StoreQuantity = (Int32)StoreRow["StoreNumber"];
            }

            if (StoreQuantity > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// Returns the boolean state if translations were found in the database.
        /// </summary>
        /// <param name="pStoreName"></param>
        /// <param name="pStreet"></param>
        /// <param name="pPostcode"></param>
        /// <param name="pCity"></param>
        /// <param name="pCountry"></param>
        /// <returns>Returns if a translations like this exists.</returns>
        public Boolean TranslationExists(String pFormName, String pControlName, Int32 pIDLanguage)
        {
            Int32 TranslationQuantity = 0;
            clsQuery Query = new clsQuery("SELECT COUNT(*) AS TranslationNumber FROM Translations WHERE FormName = '" + pFormName + "' AND ControlName = '" + pControlName + "' AND IDLanguages = " + pIDLanguage + ";", "Count translations.");
            DataTable Translations = this.Database.GetTable(Query);
            foreach (DataRow Translation in Translations.Rows)
            {
                TranslationQuantity = Convert.ToInt32(Translation["TranslationNumber"]);
            }

            if (TranslationQuantity > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        public Boolean LanguageExists(String pLanguage)
        {
            Int32 LanguageQuantity = 0;
            clsQuery Query = new clsQuery("SELECT COUNT(*) AS LanguageNumber FROM Languages WHERE Language = '" + pLanguage + "';", "Count languages.");
            DataTable LanguageNumber = this.Database.GetTable(Query);
            foreach (DataRow LanguageRow in LanguageNumber.Rows)
            {
                LanguageQuantity = Convert.ToInt32(LanguageRow["LanguageNumber"]);
            }

            if (LanguageQuantity > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// Returns the boolean state if users found in the database.
        /// </summary>
        /// <param name="pFirstName"></param>
        /// <param name="pSecondName"></param>
        /// <returns>Returns if a user like this exists.</returns>
        public Boolean UserExists(String pFirstName, String pSecondName)
        {
            Int32 UserQuantity = 0;
            clsQuery Query = new clsQuery("SELECT COUNT(*) AS UserNumber FROM Users WHERE FirstName = '" + pFirstName + "' AND SecondName = '" + pSecondName + "';", "Count users.");
            DataTable UserNumber = this.Database.GetTable(Query);
            foreach (DataRow UserRow in UserNumber.Rows)
            {
                UserQuantity = Convert.ToInt32(UserRow["UserNumber"]);
            }

            if (UserQuantity > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// Inserts a user into the database.
        /// </summary>
        /// <param name="pFirstName"></param>
        /// <param name="pSecondName"></param>
        /// <param name="pPassword"></param>
        /// <returns>The state of success.</returns>
        public Boolean InsertUser(String pFirstName, String pSecondName, String pPassword, clsLanguage pLanguage, clsCurrency pCurrency)
        {
            clsQuery Query = new clsQuery("INSERT INTO Users (DateCreated, DateChanged, FirstName, SecondName, Password, IDLanguages, IDCurrencies) VALUES (" + this.SQLTranslation.ActualDate + ", " + this.SQLTranslation.ActualDate + ", '" + pFirstName + "', '" + pSecondName + "', '" + pPassword + "', " + pLanguage.ID + ", " + pCurrency.ID + ");", "Insert user.");
            if (this.Database.ExecuteQuery(Query) == 1)
            {
                return true;
            }
            else
            {
                throw new ArgumentException("Error 4: Error inserting the user.");
            }
        }

        /// <summary>
        /// Deletes all tables in the database.
        /// </summary>
        /// <returns>The state of action.</returns>
        public Boolean EmptyDatabase()
        {
            try
            {
                ArrayList QueryCollection = new ArrayList();
                QueryCollection.Add(new clsQuery("DROP TABLE Preferences;", "Delete the preferences table."));
                QueryCollection.Add(new clsQuery("DROP TABLE Users;", "Delete the users table."));
                QueryCollection.Add(new clsQuery("DROP TABLE UsersRights;", "Delete the users table."));
                QueryCollection.Add(new clsQuery("DROP TABLE Stores;", "Delete the stores table."));
                QueryCollection.Add(new clsQuery("DROP TABLE Languages;", "Delete the languages table."));
                QueryCollection.Add(new clsQuery("DROP TABLE Translations;", "Delete the translations table."));
                QueryCollection.Add(new clsQuery("DROP TABLE Finances;", "Delete the finances table."));
                QueryCollection.Add(new clsQuery("DROP TABLE Currencies;", "Delete the currency table."));
                ExecuteQueryCollection(QueryCollection);
                return true;
            }
            catch
            {
                return false;
            }
        }

        /// <summary>
        /// Gets all the stores from the database.
        /// </summary>
        /// <returns></returns>
        public ArrayList GetStores(String pSearchArgument, Boolean pIncludeClosed)
        {
            ArrayList StoreCollection = new ArrayList();
            String AdditionalConstraint = "";
            if (pIncludeClosed)
            {
                AdditionalConstraint = "";
            }
            else
            {
                AdditionalConstraint = " AND Closed < 1";
            }
            clsQuery Query = new clsQuery("SELECT " + this.SQLTranslation.GetDateField("DateCreated") + ", " + this.SQLTranslation.GetDateField("DateChanged") + ", IDUserCreated, IDUserChanged, ID, StoreName, Street, Postcode, City, Country, Closed FROM Stores WHERE StoreName LIKE '" + pSearchArgument + "%'" + AdditionalConstraint + " ORDER BY StoreName, City, Street;", "Get all stores.");
            DataTable Stores = this.Database.GetTable(Query);
            foreach (DataRow Store in Stores.Rows)
            {
                clsStore StoreDetails = new clsStore();
                StoreDetails.DateCreated = Convert.ToDateTime(Store["DateCreated"]);
                StoreDetails.DateChanged = Convert.ToDateTime(Store["DateChanged"]);
                StoreDetails.UserCreated = GetUserName((Int32)Store["IDUserCreated"]);
                StoreDetails.UserChanged = GetUserName((Int32)Store["IDUserChanged"]);
                StoreDetails.ID = (Int32)Store["ID"];
                StoreDetails.StoreName = (String)Store["StoreName"];
                StoreDetails.Street = (String)Store["Street"];
                StoreDetails.Postcode = (String)Store["Postcode"];
                StoreDetails.City = (String)Store["City"];
                StoreDetails.Country = (String)Store["Country"];
                StoreDetails.Closed = ConvertNumberToBoolean(Store["Closed"]);
                StoreCollection.Add(StoreDetails);
            }
            return StoreCollection;
        }

        /// <summary>
        /// Gets all the finances from the database.
        /// </summary>
        /// <returns></returns>
        public ArrayList GetFinances()
        {
            ArrayList FinanceCollection = new ArrayList();
            clsQuery Query = new clsQuery("SELECT " + this.SQLTranslation.GetDateField("DateCreated") + ", " + this.SQLTranslation.GetDateField("DateChanged") + ", IDUserCreated, IDUserChanged, ID, DateConcerned, Amount, Description, IDCurrencies FROM Finances ORDER BY DateConcerned;", "Get all stores.");
            DataTable Finances = this.Database.GetTable(Query);
            foreach (DataRow Finance in Finances.Rows)
            {
                clsFinance FinanceDetails = new clsFinance();
                FinanceDetails.DateCreated = Convert.ToDateTime(Finance["DateCreated"]);
                FinanceDetails.DateChanged = Convert.ToDateTime(Finance["DateChanged"]);
                FinanceDetails.UserCreated = GetUserName((Int32)Finance["IDUserCreated"]);
                FinanceDetails.UserChanged = GetUserName((Int32)Finance["IDUserChanged"]);
                FinanceDetails.ID = (Int32)Finance["ID"];
                FinanceDetails.SetDateConcerned(Convert.ToDateTime(Finance["DateConcerned"]));
                FinanceDetails.Amount = Convert.ToDouble(Finance["Amount"]);
                FinanceDetails.Description = (String)Finance["Description"];
                FinanceDetails.Currency = GetCurrency((Int32)Finance["IDCurrencies"]);
                FinanceCollection.Add(FinanceDetails);
            }
            return FinanceCollection;
        }

        public clsControl GetControl(String pFormName, String pControlName, Int32 pIDLanguages)
        {
            clsControl ControlDetails = new clsControl();
            clsQuery Query = new clsQuery("SELECT " + this.SQLTranslation.GetDateField("DateCreated") + ", " + this.SQLTranslation.GetDateField("DateChanged") + ", IDUserCreated, IDUserChanged, ID, Translation FROM Translations WHERE FormName = '" + pFormName + "' AND ControlName = '" + pControlName + "' AND IDLanguages = " + pIDLanguages + ";", "Get control details.");
            DataTable Table = this.Database.GetTable(Query);
            if (Table.Rows.Count == 1)
            {
                foreach (DataRow Row in Table.Rows)
                {
                    ControlDetails.FormName = pFormName;
                    ControlDetails.ControlName = pControlName;
                    ControlDetails.DateCreated = Convert.ToDateTime(Row["DateCreated"]);
                    ControlDetails.DateChanged = Convert.ToDateTime(Row["DateChanged"]);
                    ControlDetails.UserCreated = GetUserName((Int32)Row["IDUserCreated"]);
                    ControlDetails.UserChanged = GetUserName((Int32)Row["IDUserChanged"]);
                    ControlDetails.ID = (Int32)Row["ID"];
                    ControlDetails.Translation = (String)Row["Translation"];
                }
            }
            return ControlDetails;
        }

        /// <summary>
        /// Gets all the controls from the database.
        /// </summary>
        /// <returns></returns>
        public ArrayList GetControls(String pFormName)
        {
            ArrayList ControlCollection = new ArrayList();

            clsQuery Query = new clsQuery("SELECT ControlName FROM Translations WHERE FormName = '" + pFormName + "' GROUP BY ControlName ORDER BY ControlName;", "Get controls.");
            DataTable Controls = this.Database.GetTable(Query);
            foreach (DataRow ControlRow in Controls.Rows)
            {
                clsControl ControlDetails = new clsControl();
                ControlDetails.ControlName = (String)ControlRow["ControlName"];
                ControlCollection.Add(ControlDetails);
            }
            return ControlCollection;
        }

        /// <summary>
        /// Gets all the users from the database.
        /// </summary>
        /// <returns>A list of users.</returns>
        public ArrayList GetAllUsers()
        {
            ArrayList UserCollection = new ArrayList();
            clsQuery Query = new clsQuery("SELECT " + this.SQLTranslation.GetDateField("DateCreated") + ", " + this.SQLTranslation.GetDateField("DateChanged") + ", ID, FirstName, SecondName, Password, IDLanguages, IDCurrencies FROM Users ORDER BY FirstName, SecondName;", "Get all users.");
            DataTable Users = this.Database.GetTable(Query);
            foreach (DataRow User in Users.Rows)
            {
                clsUser UserDetails = new clsUser();
                UserDetails.DateCreated = Convert.ToDateTime(User["DateCreated"]);
                UserDetails.DateChanged = Convert.ToDateTime(User["DateChanged"]);
                UserDetails.ID = (Int32)User["ID"];
                UserDetails.FirstName = (String)User["FirstName"];
                UserDetails.SecondName = (String)User["SecondName"];
                UserDetails.Password = (String)User["Password"];
                try
                {
                    UserDetails.Currency = GetCurrency((Int32)User["IDCurrencies"]);
                }
                catch
                {
                }
                Int32 IDLanguages;
                try
                {
                    IDLanguages = (Int32)User["IDLanguages"];
                }
                catch
                {
                    IDLanguages = 1;
                }
                UserDetails.Language = GetLanguage(IDLanguages);
                UserCollection.Add(UserDetails);
            }
            return UserCollection;
        }

        /// <summary>
        /// Gets all the languages from the database.
        /// </summary>
        /// <returns></returns>
        public ArrayList GetAllLanguages()
        {
            ArrayList LanguageCollection = new ArrayList();
            clsQuery Query = new clsQuery("SELECT " + this.SQLTranslation.GetDateField("DateCreated") + ", " + this.SQLTranslation.GetDateField("DateChanged") + ", IDUserCreated, IDUserChanged, ID, Language FROM Languages ORDER BY Language;", "Get all languages.");
            DataTable Languages = this.Database.GetTable(Query);
            foreach (DataRow Language in Languages.Rows)
            {
                clsLanguage LanguageDetails = new clsLanguage();
                LanguageDetails.DateCreated = Convert.ToDateTime(Language["DateCreated"]);
                LanguageDetails.DateChanged = Convert.ToDateTime(Language["DateChanged"]);
                LanguageDetails.UserCreated = GetUserName((Int32)Language["IDUserCreated"]);
                LanguageDetails.UserChanged = GetUserName((Int32)Language["IDUserChanged"]);
                LanguageDetails.ID = (Int32)Language["ID"];
                LanguageDetails.Language = (String)Language["Language"];
                LanguageCollection.Add(LanguageDetails);
            }
            return LanguageCollection;
        }

        /// <summary>
        /// Gets all the forms from the database.
        /// </summary>
        /// <returns></returns>
        public ArrayList GetAllForms()
        {
            ArrayList FormCollection = new ArrayList();
            clsQuery Query = new clsQuery("SELECT FormName FROM Translations GROUP BY FormName ORDER BY FormName;", "Get all forms.");
            DataTable Forms = this.Database.GetTable(Query);
            foreach (DataRow FormRow in Forms.Rows)
            {
                clsForm FormDetails = new clsForm();
                FormDetails.FormName = (String)FormRow["FormName"];
                FormCollection.Add(FormDetails);
            }
            return FormCollection;
        }

        /// <summary>
        /// Gets all the currency names from the database.
        /// </summary>
        /// <returns></returns>
        public ArrayList GetAllLatestCurrencies()
        {
            ArrayList CurrencyCollection = new ArrayList();
            String SQL = "SELECT " + this.SQLTranslation.GetDateField("DateCreated") + ", " + this.SQLTranslation.GetDateField("DateChanged") + ", IDUserCreated, IDUserChanged, ID, CurrencyName, CurrencyAbbrevation, ExchangeRateToOneEUR, " + this.SQLTranslation.GetDateField("NewestDate") + " ";
            SQL += "FROM ";
            SQL += "(";
            SQL += "SELECT CurrenciesMain.DateCreated, CurrenciesMain.DateChanged, CurrenciesMain.IDUserCreated, CurrenciesMain.IDUserChanged, CurrenciesMain.ID, CurrenciesMain.CurrencyName, CurrenciesMain.CurrencyAbbrevation, CurrenciesMain.ExchangeRateToOneEUR, CurrenciesDetails.NewestDate ";
            SQL += "FROM Currencies AS CurrenciesMain LEFT JOIN ";
            SQL += "(";
            SQL += "SELECT CurrencyAbbrevation, MAX(DateEffectiveFrom) AS NewestDate ";
            SQL += "FROM Currencies ";
            SQL += "GROUP BY CurrencyAbbrevation";
            SQL += ") ";
            SQL += "CurrenciesDetails ON CurrenciesMain.CurrencyAbbrevation = CurrenciesDetails.CurrencyAbbrevation AND ";
            SQL += "CurrenciesMain.DateEffectiveFrom = CurrenciesDetails.NewestDate";
            SQL += ") ";
            SQL += "CurrenciesComplete ";
            SQL += "WHERE CurrenciesComplete.NewestDate IS NOT NULL;";
            clsQuery Query = new clsQuery(SQL, "Get all latest currencies.");
            DataTable Currencies = this.Database.GetTable(Query);
            foreach(DataRow CurrencyRow in Currencies.Rows)
            {
                clsCurrency Currency = new clsCurrency();
                Currency.ID = (Int32)CurrencyRow["ID"];
                Currency.DateCreated = Convert.ToDateTime(CurrencyRow["DateCreated"]);
                Currency.DateChanged = Convert.ToDateTime(CurrencyRow["DateChanged"]);
                Currency.UserCreated = GetUserName((Int32)CurrencyRow["IDUserCreated"]);
                Currency.UserChanged = GetUserName((Int32)CurrencyRow["IDUserChanged"]);
                Currency.CurrencyName = (String)CurrencyRow["CurrencyName"];
                Currency.CurrencyAbbrevation = (String)CurrencyRow["CurrencyAbbrevation"];
                Currency.CurrencyValue = Convert.ToDouble(CurrencyRow["ExchangeRateToOneEUR"]);
                Currency.EffectiveFrom = Convert.ToDateTime(CurrencyRow["NewestDate"]);
                CurrencyCollection.Add(Currency);
            }

            return CurrencyCollection;
        }

        /// <summary>
        /// Gets all the currencies from the database.
        /// </summary>
        /// <returns></returns>
        public ArrayList GetAllCurrencies()
        {
            ArrayList CurrencyCollection = new ArrayList();
            clsQuery Query = new clsQuery("SELECT " + this.SQLTranslation.GetDateField("DateCreated") + ", " + this.SQLTranslation.GetDateField("DateChanged") + ", IDUserCreated, IDUserChanged, ID, CurrencyName, CurrencyAbbrevation, ExchangeRateToOneEUR, DateEffectiveFrom FROM Currencies ORDER BY CurrencyName, DateEffectiveFrom DESC;", "Get all currencies.");
            DataTable Currencies = this.Database.GetTable(Query);

            clsCurrency CurrencyPast = new clsCurrency();
            foreach (DataRow CurrencyRow in Currencies.Rows)
            {
                String CurrencyName = (String)CurrencyRow["CurrencyName"];
                String CurrencyAbbrevation = (String)CurrencyRow["CurrencyAbbrevation"];
                DateTime DateCreated = Convert.ToDateTime(CurrencyRow["DateCreated"]);
                DateTime DateChanged = Convert.ToDateTime(CurrencyRow["DateChanged"]);
                String UserCreated = GetUserName((Int32)CurrencyRow["IDUserCreated"]);
                String UserChanged = GetUserName((Int32)CurrencyRow["IDUserChanged"]);
                Int32 ID = (Int32)CurrencyRow["ID"];
                Double CurrencyValue = Convert.ToDouble(CurrencyRow["ExchangeRateToOneEUR"]);
                DateTime EffectiveFrom = Convert.ToDateTime(CurrencyRow["DateEffectiveFrom"]);

                clsCurrency CurrencyMain = new clsCurrency();
                if (CurrencyPast.CurrencyName != null && CurrencyPast.CurrencyName == CurrencyName)
                {
                    CurrencyMain = CurrencyPast;
                }
                else
                {
                    if (CurrencyPast.CurrencyName != null)
                    {
                        CurrencyCollection.Add(CurrencyPast);
                    }
                }

                clsCurrency CurrencyDetails = new clsCurrency();

                CurrencyMain.CurrencyName = CurrencyName;
                CurrencyMain.CurrencyAbbrevation = CurrencyAbbrevation;

                CurrencyDetails.CurrencyAbbrevation = CurrencyAbbrevation;
                CurrencyDetails.CurrencyName = CurrencyName;
                CurrencyDetails.CurrencyValue = CurrencyValue;
                CurrencyDetails.DateChanged = DateChanged;
                CurrencyDetails.DateCreated = DateCreated;
                CurrencyDetails.EffectiveFrom = EffectiveFrom;
                CurrencyDetails.ID = ID;
                CurrencyDetails.UserChanged = UserChanged;
                CurrencyDetails.UserCreated = UserCreated;

                CurrencyMain.SetCurrencyValue(CurrencyDetails);

                CurrencyPast = CurrencyMain;
            }

            if (CurrencyPast.CurrencyName != null)
            {
                CurrencyCollection.Add(CurrencyPast);
            }

            return CurrencyCollection;
        }

        /// <summary>
        /// Check if users are in the database.
        /// </summary>
        /// <returns></returns>
        public Boolean UsersExists()
        {
            Boolean UserInDatabase = false;
            clsQuery Query = new clsQuery("SELECT COUNT(*) AS UserNumber FROM Users;", "Count users.");
            DataTable UserNumber = this.Database.GetTable(Query);
            foreach (DataRow User in UserNumber.Rows)
            {
                if (Convert.ToInt32(User["UserNumber"]) > 0)
                {
                    UserInDatabase = true;
                }
            }
            return UserInDatabase;
        }

        /// <summary>
        /// Executes a SQL query.
        /// </summary>
        /// <param name="pQuery">The SQL query.</param>
        public void ExecuteQuery(clsQuery pQuery)
        {
            Database.ExecuteQuery(pQuery);
        }

        /// <summary>
        /// Executes a SQL query collection.
        /// </summary>
        /// <param name="pQueryCollection">The query collection.</param>
        public void ExecuteQueryCollection(ArrayList pQueryCollection)
        {
            foreach (clsQuery Query in pQueryCollection)
            {
                ExecuteQuery(Query);
            }
        }

        /// <summary>
        /// Determines the state of the existence of the configuration file.
        /// If yes, the configuration file is readout.
        /// </summary>
        /// <returns>The state.</returns>
        public Boolean LoadConfiguration()
        {
            //Settings file exists?
            if (this.Settings.ConfigurationFileExists())
            {
                //Load the settings.
                if (this.Settings.LoadSettings())
                {
                    InitiateDatabase();
                    return true;
                }
                else
                {
                    return false;
                }
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// Starts the update process.
        /// </summary>
        /// <returns>The state of success.</returns>
        public Boolean StartUpdate()
        {
            clsDatabaseUpdate DatabaseUpdate = new clsDatabaseUpdate(this);
            if (DatabaseUpdate.Start())
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// Tests whether the connection is possible with the given parameters.
        /// </summary>
        /// <param name="pDatabaseChoice"></param>
        /// <param name="pDbAddress"></param>
        /// <param name="pDbUserName"></param>
        /// <param name="pDbPassword"></param>
        /// <param name="pDatabaseName"></param>
        /// <returns></returns>
        public Boolean DatabaseConnectable(clsSettings.enumDatabaseChoice pDatabaseChoice, String pDbAddress, String pDbUserName, String pDbPassword, String pDatabaseName)
        {
            clsDatabase DatabaseTest = new clsDatabase(GetEnumDatabase(pDatabaseChoice), pDbAddress, pDbUserName, pDbPassword, pDatabaseName);
            return DatabaseTest.IsConnectable();
        }

        /// <summary>
        /// Gets the actual database version.
        /// </summary>
        /// <returns>The database version.</returns>
        public Int32 DatabaseVersion()
        {
            try
            {
                clsQuery Query = Database.Select("SELECT DatabaseVersion FROM Preferences;", "Get the database version.");
                DataTable Table = Database.GetTable(Query);
                if (Table.Rows.Count > 1)
                {
                    throw new ArgumentException("Error 3");
                }
                else
                {
                    this.Settings.DatabaseVersion_Database = Convert.ToInt32(Table.Rows[0]["DatabaseVersion"]);
                }
                return this.Settings.DatabaseVersion_Database;
            }
            catch
            {
                return 0;
            }
        }
        #endregion

        #region Private methods
        /// <summary>
        /// Converts a DateTime object to a database date field.
        /// </summary>
        /// <param name="pDouble"></param>
        /// <returns></returns>
        private String DoubleToDatabaseString(Double pDouble)
        {
            return pDouble.ToString().Replace(',', '.');
        }

        /// <summary>
        /// Assures the 0 (if single number) infront of a date number.
        /// </summary>
        /// <param name="pDateNumber"></param>
        /// <returns></returns>
        private String DateNumber(Int32 pDateNumber)
        {
            String NumberString = pDateNumber.ToString();
            if (NumberString.Length == 2)
            {
                return NumberString;
            }
            else
            {
                return "0" + NumberString;
            }
        }

        /// <summary>
        /// Gets a language by ID.
        /// </summary>
        /// <param name="IDLanguages"></param>
        /// <returns></returns>
        private clsLanguage GetLanguage(Int32 IDLanguages)
        {
            clsQuery Query = new clsQuery("SELECT * FROM Languages WHERE ID = " + IDLanguages + ";", "Get a language.");
            DataTable Table = Database.GetTable(Query);
            clsLanguage Language = new clsLanguage();
            Language.ID = IDLanguages;
            foreach (DataRow Row in Table.Rows)
            {
                Language.Language = (String)Row["Language"];
            }
            return Language;
        }

        private clsCurrency GetCurrency(Int32 IDCurrency)
        {
            clsQuery Query = new clsQuery("SELECT * FROM Currencies WHERE ID = " + IDCurrency + ";", "Get a currency.");
            DataTable Table = Database.GetTable(Query);
            clsCurrency Currency = new clsCurrency();
            Currency.ID = IDCurrency;
            foreach (DataRow Row in Table.Rows)
            {
                Currency.CurrencyName = (String)Row["CurrencyName"];
                Currency.CurrencyAbbrevation = (String)Row["CurrencyAbbrevation"];
                Currency.DateCreated = Convert.ToDateTime(Row["DateCreated"]);
                Currency.DateChanged = Convert.ToDateTime(Row["DateChanged"]);
                Currency.UserCreated = GetUserName((Int32)Row["IDUserCreated"]);
                Currency.UserChanged = GetUserName((Int32)Row["IDUserChanged"]);
                Currency.CurrencyValue = Convert.ToDouble(Row["ExchangeRateToOneEUR"]);
                Currency.EffectiveFrom = Convert.ToDateTime(Row["DateEffectiveFrom"]);
            }
            return Currency;
        }

        /// <summary>
        /// Gets the language ID from the specefic user. Returns 1 if none was found.
        /// </summary>
        /// <param name="pIDUsers"></param>
        /// <returns>The ID of the language.</returns>
        private Int32 GetIDLanguages(Int32 pIDUsers)
        {
            Int32 IDLanguages = 1;
            if (pIDUsers > 0)
            {
                clsQuery Query = new clsQuery("SELECT IDLanguages FROM Users WHERE ID = " + pIDUsers + ";", "Get language information.");
                DataTable Table = Database.GetTable(Query);
                foreach (DataRow Row in Table.Rows)
                {
                    IDLanguages = (Int32)Row["IDLanguages"];
                }
            }
            return IDLanguages;
        }

        /// <summary>
        /// Gets the username by his ID.
        /// </summary>
        /// <param name="pIDUser"></param>
        /// <returns>The username.</returns>
        private String GetUserName(Int32 pIDUsers)
        {
            String UserName = "";
            if (pIDUsers > 0)
            {
                clsQuery Query = Database.Select("SELECT FirstName, SecondName FROM Users WHERE ID = " + pIDUsers + ";", "Get username.");
                DataTable Table = Database.GetTable(Query);
                foreach (DataRow Row in Table.Rows)
                {
                    UserName = (String)Row["FirstName"] + " " + (String)Row["SecondName"];
                }
            }
            else
            {
                UserName = "System";
            }
            return UserName;
        }

        /// <summary>
        /// Converts a Int32 to Boolean type.
        /// </summary>
        /// <param name="pNumber"></param>
        /// <returns></returns>
        private Boolean ConvertNumberToBoolean(Object pNumber)
        {
            Int32 Number = 0;
            try
            {
                Number = (Int32)pNumber;
            }
            catch
            {
            }

            if (Number == 1)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// Converts a Boolean to Int32 type.
        /// </summary>
        /// <param name="pBool"></param>
        /// <returns></returns>
        private Int32 ConvertBooleanToNumber(Boolean pBool)
        {
            if (pBool)
            {
                return 1;
            }
            else
            {
                return 0;
            }
        }

        /// <summary>
        /// Converts from enumDatabaseChoice to clsDatabase.enumDatabase.
        /// </summary>
        /// <param name="pDatabaseChoice">The converted type.</param>
        /// <returns></returns>
        private clsDatabase.enumDatabase GetEnumDatabase(clsSettings.enumDatabaseChoice pDatabaseChoice)
        {
            switch (pDatabaseChoice)
            {
                case clsSettings.enumDatabaseChoice.MicrosoftSQLServer2000:
                    return clsDatabase.enumDatabase.eSQLServer2000;
                case clsSettings.enumDatabaseChoice.MySQLServer:
                    return clsDatabase.enumDatabase.eMySQL;
                default:
                    //TODO: Find a system for error handling.
                    throw new ArgumentException("Error 1");
            }
        }

        /// <summary>
        /// Initiates the Database object.
        /// </summary>
        private void InitiateDatabase()
        {
            //Instantiate the database object ot be usable.
            if (this.Database == null)
            {
                this.Database = new clsDatabase(GetEnumDatabase(this.Settings.DatabaseChoice), this.Settings.DatabaseAddress, this.Settings.UserName_Database, this.Settings.Password_Database, this.Settings.DatabaseName);
                this.SQLTranslation = new clsSQLTranslation(GetEnumDatabase(this.Settings.DatabaseChoice));
            }
        }
        #endregion
    }
}
